'''
编程实现：使用openpyxl在excel中写入10列2000行的数据，然后使用openpyxl读取出来并使用pymysql写入到数据库中。
'''
import openpyxl
import pymysql


class ExcelOption:
    def __init__(self, filename, title, index):
        self.index = index
        self.title = title
        self.filename = filename
        self.workbook = openpyxl.Workbook()

    def write_to_excel(self):
        self.workbook.create_sheet(self.title, self.index)
        sheet = self.workbook[self.title]
        for i in range(2000):
            sheet.append(
                [f"{i}", f"idCard_{i}", f"name_{i}", f"realName_{i}", f"pwd_{i}", f"tel_{i}",
                 f"email_{i}", i % 60, "男" if i % 2 == 1 else "女", f"address_{i}"])
        self.workbook.save(self.filename)

    def read_from_excel(self):
        sheet = self.workbook[self.title]
        data = [[sheet.cell(row=i, column=j).value for j in range(1, sheet.max_column + 1)]
                for i in range(1, sheet.max_row + 1)]
        return data


class MysqlOption:
    def __init__(self, pwd, db, host="localhost", port=3306, user="root"):
        self.db = pymysql.connect(host=host, port=port, user=user, password=pwd, database=db)

    # def __del__(self):
    #     self.db.close()

    def read_from_list(self, data):
        with self.db.cursor() as cursor:
            sql = '''
            INSERT INTO `user_info`(
            `id`, `idcard`, `username`, `realname`, `pwd`, `telphone`, `email`, `age`, `sex`, `address`) VALUES 
            (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            '''
            try:
                # for i in data:
                #     cursor.execute(sql, (i))
                cursor.executemany(sql, data)
            except Exception as e:
                print("数据库插入失败！原因: ", e)
                self.db.rollback()
            print("数据库插入成功！")
            self.db.commit()


if __name__ == '__main__':
    eo = ExcelOption('userInfo.xlsx', "userInfo", 0)
    eo.write_to_excel()
    data = eo.read_from_excel()
    mo = MysqlOption("123456", "study")
    mo.read_from_list(data)
